--    Program: 		CAPPOEXT_ORGANIZATION_ACCESS.sql
--    Description:	Extract PO Organization Access information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare
    l_processed_count		    number;
    l_rejected_count 		    number;
    l_err_msg				  	varchar2(2000);
    l_name				  		varchar2(200);
    
    l_filePath           		varchar2(200);
    l_fileName           		varchar2(100);
    
    l_set_of_books_id           number;
    
    cursor cur_org (p_set_of_books_id number) is
       select mp.* 
	   from   mtl_parameters mp, 
	          org_organization_definitions ood
	   where  mp.organization_id = ood.organization_id
	   and    ood.set_of_books_id = p_set_of_books_id;
              
    l_utl_file                  utl_file.file_type;
begin  
   l_filePath := '/tmp'  ;
   l_fileName := 'CAPPO_ORGANIZATION_ACCESS.dat';

   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_name := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_name);  
   exception
      when others then
        dbms_output.put_line(l_name||': set of books name not found');
   end; 
  
   l_utl_file := utl_file.fopen(l_filePath,l_fileName,'w');
     
   for cur in cur_org(l_set_of_books_id) loop
       utl_file.put_line(l_utl_file,
       cur.ORGANIZATION_ID                 || chr(9) ||
       cur.LAST_UPDATE_DATE                || chr(9) ||
       cur.LAST_UPDATED_BY                 || chr(9) ||
       cur.CREATION_DATE                   || chr(9) ||
       cur.CREATED_BY                      || chr(9) ||
       cur.LAST_UPDATE_LOGIN               || chr(9) ||
       cur.ORGANIZATION_CODE               || chr(9) ||
       cur.MASTER_ORGANIZATION_ID          || chr(9) ||
       cur.PRIMARY_COST_METHOD             || chr(9) ||
       cur.COST_ORGANIZATION_ID            || chr(9) ||
       cur.DEFAULT_MATERIAL_COST_ID        || chr(9) ||
       cur.CALENDAR_EXCEPTION_SET_ID       || chr(9) ||
       cur.CALENDAR_CODE                   || chr(9) ||
       cur.GENERAL_LEDGER_UPDATE_CODE      || chr(9) ||
       cur.DEFAULT_ATP_RULE_ID             || chr(9) ||
       cur.DEFAULT_PICKING_RULE_ID         || chr(9) ||
       cur.DEFAULT_LOCATOR_ORDER_VALUE     || chr(9) ||
       cur.DEFAULT_SUBINV_ORDER_VALUE      || chr(9) ||
       cur.NEGATIVE_INV_RECEIPT_CODE       || chr(9) ||
       cur.STOCK_LOCATOR_CONTROL_CODE      || chr(9) ||
       cur.MATERIAL_ACCOUNT                || chr(9) ||
       cur.MATERIAL_OVERHEAD_ACCOUNT       || chr(9) ||
       cur.MATL_OVHD_ABSORPTION_ACCT       || chr(9) ||
       cur.RESOURCE_ACCOUNT                || chr(9) ||
       cur.PURCHASE_PRICE_VAR_ACCOUNT      || chr(9) ||
       cur.AP_ACCRUAL_ACCOUNT              || chr(9) ||
       cur.OVERHEAD_ACCOUNT                || chr(9) ||
       cur.OUTSIDE_PROCESSING_ACCOUNT      || chr(9) ||
       cur.INTRANSIT_INV_ACCOUNT           || chr(9) ||
       cur.INTERORG_RECEIVABLES_ACCOUNT    || chr(9) ||
       cur.INTERORG_PRICE_VAR_ACCOUNT      || chr(9) ||
       cur.INTERORG_PAYABLES_ACCOUNT       || chr(9) ||
       cur.COST_OF_SALES_ACCOUNT           || chr(9) ||
       cur.ENCUMBRANCE_ACCOUNT             || chr(9) ||
       cur.PROJECT_COST_ACCOUNT            || chr(9) ||
       cur.INTERORG_TRANSFER_CR_ACCOUNT    || chr(9) ||
       cur.MATL_INTERORG_TRANSFER_CODE     || chr(9) ||
       cur.INTERORG_TRNSFR_CHARGE_PERCENT  || chr(9) ||
       cur.SOURCE_ORGANIZATION_ID          || chr(9) ||
       cur.SOURCE_SUBINVENTORY             || chr(9) ||
       cur.SOURCE_TYPE                     || chr(9) ||
       cur.ORG_MAX_WEIGHT                  || chr(9) ||
       cur.ORG_MAX_WEIGHT_UOM_CODE         || chr(9) ||
       cur.ORG_MAX_VOLUME                  || chr(9) ||
       cur.ORG_MAX_VOLUME_UOM_CODE         || chr(9) ||
       cur.SERIAL_NUMBER_TYPE              || chr(9) ||
       cur.AUTO_SERIAL_ALPHA_PREFIX        || chr(9) ||
       cur.START_AUTO_SERIAL_NUMBER        || chr(9) ||
       cur.AUTO_LOT_ALPHA_PREFIX           || chr(9) ||
       cur.LOT_NUMBER_UNIQUENESS           || chr(9) ||
       cur.LOT_NUMBER_GENERATION           || chr(9) ||
       cur.LOT_NUMBER_ZERO_PADDING         || chr(9) ||
       cur.LOT_NUMBER_LENGTH               || chr(9) ||
       cur.STARTING_REVISION               || chr(9) ||
       cur.ATTRIBUTE_CATEGORY              || chr(9) ||
       cur.ATTRIBUTE1                      || chr(9) ||
       cur.ATTRIBUTE2                      || chr(9) ||
       cur.ATTRIBUTE3                      || chr(9) ||
       cur.ATTRIBUTE4                      || chr(9) ||
       cur.ATTRIBUTE5                      || chr(9) ||
       cur.ATTRIBUTE6                      || chr(9) ||
       cur.ATTRIBUTE7                      || chr(9) ||
       cur.ATTRIBUTE8                      || chr(9) ||
       cur.ATTRIBUTE9                      || chr(9) ||
       cur.ATTRIBUTE10                     || chr(9) ||
       cur.ATTRIBUTE11                     || chr(9) ||
       cur.ATTRIBUTE12                     || chr(9) ||
       cur.ATTRIBUTE13                     || chr(9) ||
       cur.ATTRIBUTE14                     || chr(9) ||
       cur.ATTRIBUTE15                     || chr(9) ||
       cur.DEFAULT_DEMAND_CLASS            || chr(9) ||
       cur.ENCUMBRANCE_REVERSAL_FLAG       || chr(9) ||
       cur.MAINTAIN_FIFO_QTY_STACK_TYPE    || chr(9) ||
       cur.INVOICE_PRICE_VAR_ACCOUNT       || chr(9) ||
       cur.AVERAGE_COST_VAR_ACCOUNT        || chr(9) ||
       cur.SALES_ACCOUNT                   || chr(9) ||
       cur.EXPENSE_ACCOUNT                 || chr(9) ||
       cur.SERIAL_NUMBER_GENERATION        || chr(9) ||
       cur.REQUEST_ID                      || chr(9) ||
       cur.PROGRAM_APPLICATION_ID          || chr(9) ||
       cur.PROGRAM_ID                      || chr(9) ||
       cur.PROGRAM_UPDATE_DATE             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE_CATEGORY       || chr(9) ||
       cur.GLOBAL_ATTRIBUTE1               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE2               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE3               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE4               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE5               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE6               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE7               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE8               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE9               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE10              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE11              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE12              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE13              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE14              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE15              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE16              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE17              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE18              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE19              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE20              || chr(9) ||
       cur.MAT_OVHD_COST_TYPE_ID           || chr(9) ||
       cur.PROJECT_REFERENCE_ENABLED       || chr(9) ||
       cur.PM_COST_COLLECTION_ENABLED      || chr(9) ||
       cur.PROJECT_CONTROL_LEVEL           || chr(9) ||
       cur.AVG_RATES_COST_TYPE_ID          || chr(9) ||
       cur.TXN_APPROVAL_TIMEOUT_PERIOD     || chr(9) ||
       cur.MO_SOURCE_REQUIRED              || chr(9) ||
       cur.MO_PICK_CONFIRM_REQUIRED        || chr(9) ||
       cur.MO_APPROVAL_TIMEOUT_ACTION      || chr(9) ||
       cur.BORRPAY_MATL_VAR_ACCOUNT        || chr(9) ||
       cur.BORRPAY_MOH_VAR_ACCOUNT         || chr(9) ||
       cur.BORRPAY_RES_VAR_ACCOUNT         || chr(9) ||
       cur.BORRPAY_OSP_VAR_ACCOUNT         || chr(9) ||
       cur.BORRPAY_OVH_VAR_ACCOUNT         || chr(9) ||
       cur.PROCESS_ENABLED_FLAG            || chr(9) ||
       cur.PROCESS_ORGN_CODE               || chr(9) ||
       cur.WSM_ENABLED_FLAG                || chr(9) ||
       cur.DEFAULT_COST_GROUP_ID           || chr(9) ||
       cur.LPN_PREFIX                      || chr(9) ||
       cur.LPN_SUFFIX                      || chr(9) ||
       cur.LPN_STARTING_NUMBER             || chr(9) ||
       cur.WMS_ENABLED_FLAG                || chr(9) ||
       cur.PREGEN_PUTAWAY_TASKS_FLAG       || chr(9) ||
       cur.REGENERATION_INTERVAL           || chr(9) ||
       cur.TIMEZONE_ID                     || chr(9) ||
       cur.MAX_PICKS_BATCH                 || chr(9) ||
       cur.DEFAULT_WMS_PICKING_RULE_ID     || chr(9) ||
       cur.DEFAULT_PUT_AWAY_RULE_ID        || chr(9) ||
       cur.DEFAULT_TASK_ASSIGN_RULE_ID     || chr(9) ||
       cur.DEFAULT_LABEL_COMP_RULE_ID      || chr(9) ||
       cur.DEFAULT_CARTON_RULE_ID          || chr(9) ||
       cur.DEFAULT_CYC_COUNT_HEADER_ID     || chr(9) ||
       cur.CROSSDOCK_FLAG                  || chr(9) ||
       cur.CARTONIZATION_FLAG              || chr(9) ||
       cur.COST_CUTOFF_DATE                || chr(9) ||
       cur.ENABLE_COSTING_BY_CATEGORY      || chr(9) ||
       cur.COST_GROUP_ACCOUNTING           || chr(9) ||
       cur.ALLOCATE_SERIAL_FLAG            || chr(9) ||
       cur.DEFAULT_PICK_TASK_TYPE_ID       || chr(9) ||
       cur.DEFAULT_CC_TASK_TYPE_ID         || chr(9) ||
       cur.DEFAULT_PUTAWAY_TASK_TYPE_ID    || chr(9) ||
       cur.DEFAULT_REPL_TASK_TYPE_ID       || chr(9) ||
       cur.EAM_ENABLED_FLAG                || chr(9) ||
       cur.MAINT_ORGANIZATION_ID           || chr(9) ||
       cur.PRIORITIZE_WIP_JOBS             || chr(9) ||
       cur.DEFAULT_CROSSDOCK_SUBINVENTORY  || chr(9) ||
       cur.SKIP_TASK_WAITING_MINUTES       || chr(9) ||
       cur.QA_SKIPPING_INSP_FLAG           || chr(9) ||
       cur.DEFAULT_CROSSDOCK_LOCATOR_ID    || chr(9) ||
       cur.DEFAULT_MOXFER_TASK_TYPE_ID     || chr(9) ||
       cur.DEFAULT_MOISSUE_TASK_TYPE_ID    || chr(9) ||
       cur.DEFAULT_MATL_OVHD_COST_ID       || chr(9) ||
       cur.DISTRIBUTED_ORGANIZATION_FLAG   || chr(9) ||
       cur.CARRIER_MANIFESTING_FLAG        || chr(9) ||
       cur.DISTRIBUTION_ACCOUNT_ID         || chr(9) ||
       cur.DIRECT_SHIPPING_ALLOWED         || chr(9) ||
       cur.DEFAULT_PICK_OP_PLAN_ID         || chr(9) ||
       cur.MAX_CLUSTERS_ALLOWED            || chr(9) ||
       cur.CONSIGNED_FLAG                  || chr(9) ||
       cur.CARTONIZE_SALES_ORDERS          || chr(9) ||
       cur.CARTONIZE_MANUFACTURING                        
       );   
    l_processed_count := l_processed_count + 1;
  end loop;

  if l_processed_count = 0 then   
    l_rejected_count := l_rejected_count + 1;
    l_err_msg := 'PO Inventory Organization not found';
  end if;
   
  -- insert into log table
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ('CAPPOEXT_ORGANIZATION_ACCESS', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
 commit;

  utl_file.fclose(l_utl_file);
   
exception
    when others then
         dbms_output.put_line(sqlerrm);
         rollback;
   
end;
/


--exit;
